配列数式
1 配列数式とは
1 配列数式の機能
配列数式は、配列と呼ばれる複数の値またはセル参照に1つの数式を当てはめて、1つまたは複数の結果を求める計算式です。
「配列」とは本来、コンピュータ用語で「複数の行と列で構成されるデータの集まり」を意味しています。
Excelでは一般的に、複数のセルからなる範囲に1つの数式を共有しているような数式を「配列数式」、その範囲を「配列範囲」と呼んでいます。
配列数式の中で使用される配列を「配列引数」といい、同じ行数・列数からなる複数の配列引数を指定する必要があります。
配列数式を入力するには、計算式を入力後、カーソルを数式バーでアクティブの状態にして「Ctrl」キーと「Shif」tキーと「Enter」キーを同時に押します。すると、数式が
{ } で囲まれます。
左下図のデータの中で、「A商店」の金額だけを合計する場合を例に、上記の内容を説明します。
まず、数式バーに下記の数式を入力して、「Ctrl」キーと「Shif」tキーと「Enter」キーを同時に押します。
=SUM(IF(A2:A6="A商店",E2:E6))
式の意味→セルA2からA6までを調べて、「A商店」に一致したら同じ行の「金額」の数値を合計する。
| 数式バーの計算式 |
 |
|
セルへの入力結果 |
 |
計算式の中の「A2:A6」「E2:E6」が「配列引数(配列範囲)」です。右上図は、配列のイメージを表したものです。「A2:A6」と「E2:E6」それぞれが1列5行からなるデータの集まりであり、また、同じ行数・列数からなる複数の配列引数であることがわかります。
2 配列数式での計算内容
次に、配列数式ではどのように計算されるのかを上記計算式を元にイメージ図で見てみましょう。
計算式のうち、「IF(A2:A6="A商店",E2:E6)」の部分が
IF関数の計算式です。
「A2:A6="A商店"」の部分が「論理式」、「E2:E6」の部分が「真の場合」の式となります。
IF関数では、「真の場合」の式は省略できませんが、「偽の場合」の式は省略できるため、ここでは「偽の場合」の式を省略してあります。
計算の順序は、
まず最初にセルA2からA6まで、"A商店"に一致するかどうかを調べて(左上図)一致すれば「TRUE」、不一致の場合は「FALSE」を返した作業用配列(これは目で見ることはできません)がパソコン内部で作られます。(上中図)
次に、SUM関数により、「TRUE」に対応する計算(加算)対象セルE2からE6までのセルの値を合計します。(右上図)その結果、SUM(E2,E4,E5)と同じ意味となり、その値の合計
7,900 を得ることができます。
2 複数条件による配列数式
1 複数条件による計算式の書き方
配列数式では、複数条件を指定して計算式を入力することができます。ただし、一般の関数を用いた計算式では計算式の中に「AND関数」や「OR関数」を組み込むことができますが、配列数式では「AND関数」や「OR関数」は使用できません。
AND関数を用いた一般の計算式と、配列数式によるAND条件の記述の違いは次のとおりです。
AND関数・・・「AND(条件式1,条件式2)」
配列数式・・・「(条件式1)*(条件式2)」
配列数式でAND条件を記述するときは、条件を( )で括り、間に「*
(アスタリスク)」を入れます。
OR関数を用いた一般の計算式と、配列数式によるOR条件の記述の違いは次のとおりです。
OR関数・・・・「OR(条件式1,条件式2)」
配列数式・・・「(条件式1)+(条件式2)」
配列数式でOR条件を記述するときは、条件を( )で括り、間に「+
(プラス)」を入れます。
なお、AND条件による配列数式では、下記のようにIF関数をネスト(いくつか組み込む)して条件式を記述する方法もあります。
{=SUM(IF(A2:A6="A商店",IF(B2:B6="りんご",E2:E6)))}
2 個数(レコード件数)計算
一般に、個数(レコード件数)計算では「COUNT関数」が用いられますが、配列数式では「SUM関数」で簡単に複数条件を設定した個数計算を行うことができます。
下記は、品名が「みかん」または「りんご」の個数(レコード件数)の計算を行った結果です。
右下図に、「SUM関数」と「COUNT関数」の配列数式の違いを示してみました。
「SUM関数」では、条件式のみで個数が計算されているのに対して、「COUNT関数」ではさらに「IF関数」を使用して、個数計算がなされています。
「SUM関数」を使用した配列数式では、どうして条件式だけで個数を求めることができるのでしょうか。その理由を調べてみます。
「(B2:B6)="みかん")」「(B2:B6)="りんご")」のいずれの式も条件を満たしたときは、戻り値として「TRUE」が返り、条件が満たされない場合は「FALSE」が返ります。
「TRUE」や「FALSE」は「論理値」といい、パソコンの内部では「TRUE」は「1」、「FALSE」は「0
(ゼロ)」として認識され計算の対象となります。下図は、論理値の加減乗除を行った結果を表しています。
上表で「TRUE」は「1」、「FALSE」は「0 (ゼロ)」であることが分かります。除算の2番目と4番目は「0
除算エラー」が出ています。
1. OR条件の場合の計算
次に、個数(レコード件数)計算がどのように行われるかを見てみます。
まず、セルB2が「みかん」かどうかを調べ、条件を満たせば「TRUE
(1)」を、条件を満たさないときは「FALSE (0)」を返します。便宜上、TRUE、FALSEの結果を「値
1」とします。
次に、セルB2が「りんご」かどうかを調べ、条件を満たせば「TRUE
(1)」を、条件を満たさないときは「FALSE (0)」を返します。便宜上、TRUE、FALSEの結果を「値
2」とします。
「値 1」と「値 2」を加算した結果を作業用配列に格納します。セルB6までこの作業を繰返してから、SUM関数により作業用セルの値を合計します。結果は、「みかん」が
1、「りんご」が 2 の合計 3 件を求めることができます。
2. AND条件の場合の計算
上記は条件が「〜または〜」という「OR条件」による計算でしたが、参考までに「〜かつ〜」という「AND条件」による計算式を示します。
計算式例は、販売先が「A商店」かつ、品名「りんご」の個数(レコード件数)を求める配列数式です。
まず、セルA2が「A商店」かどうかを調べ、条件を満たせば「TRUE
(1)」を、条件を満たさないときは「FALSE (0)」を返します。便宜上、TRUE、FALSEの結果を「値
1」とします。
次に、セルB2が「りんご」かどうかを調べ、条件を満たせば「TRUE
(1)」を、条件を満たさないときは「FALSE (0)」を返します。便宜上、TRUE、FALSEの結果を「値
2」とします。
「値 1」と「値 2」を乗算した結果を作業用配列に格納します。セルB6までこの作業を繰返してから、SUM関数により作業用セルの値を合計します。
セルA5の検索結果はTRUE(1)ですが、B5の検索結果がFALSE(0)のため、1×0
= 0 の結果となり、合計 は 2 件となります。
3 合計計算
個数(レコード件数)計算では特に必要としない限り計算範囲を指定する必要が有りませんでしたが、合計を行う配列数式では、「IF関数」を併用して結果を求めます。
まず、合計計算と個数計算との相違を見てみます。
下記は、品名が「みかん」または「りんご」に該当するものの金額合計と個数(レコード件数)の配列数式を示したものです。
個数計算 {=SUM((A2:A6="A商店")*(B2:B6="りんご"))}
合計計算 {=SUM(IF((A2:A6="A商店")*(B2:B6="りんご"),E2:E6))}
上の合計計算の式で、赤字部分が個数計算の式と異なる部分です。E2:E6が合計を行うセルの計算範囲で、IF関数の「真の場合」の式に該当します。(「偽の場合」の式は省略しています。)
端的に言えば、個数計算にIF関数と計算範囲を加えれば合計計算の数式に変更できるわけです。
更に簡単な方法は、条件式の戻り値が「TRUE(1)」又は「FALSE(0)」になることに着目して、次のように式を簡略化することもできます。
合計計算 {=SUM((A2:A6="A商店")*(B2:B6="りんご")*(E2:E6))}
(A2:A6="A商店") と (B2:B6="りんご")
の結果は必ず「TRUE(1)」又は「FALSE(0)」となります。したがって、両方又はどちらかが「FALSE(0)」の場合は
0 が返り、0 に金額を乗じても結果は 0 となります。
どちらも「TRUE(1)」の場合は 1 が返り、1
に金額を乗じたときは、同じ行の金額の値となります。したがって、条件が成立したときだけの合計金額を求めることができるわけです。
(A2="A商店" → TRUE → 1) * (B2="りんご" →
TRUE → 1) * (C2 → 2,000) 1 * 1 * 2,000
(A3="A商店" → FALSE → 0) * (B3="りんご" →
FALSE → 0) * (C3 → 1,800) 0 * 0 * 1,800
(A4="A商店" → TRUE → 1) * (B4="りんご" →
TRUE → 1) * (C4 → 3,900) 1 * 1 * 3,900
(A5="A商店" → TRUE → 1) * (B5="りんご" →
FALSE → 0) * (C5 → 2,000) 1 * 0 * 2,000
(A6="A商店" → FALSE → 0) * (B6="りんご" →
FALSE → 0) * (C6 → 3,750) 0 * 0 * 3,750
右端の計算式がピンク色以外の数値は、計算式中に 0
を含んでおり、0 に数値を乗じても 0
になることが理解できると思います。SUM関数で合計すると、2,000+0+3,900+0+0
、結果は5,900 となります。
3 特定の行・列だけ計算する配列数式
1 特定の文字列が入力されているセルの行だけを計算する
リストには複数行で 1
件分のデータとなっているものもあります。例えば、1件分のデータの1行目に数量、2行目に単価、3行目に金額が入力されている場合などです。
このようなリストで数量だけ又は金額だけ計算する場合、条件が1つだけの合計は「SUMIF関数」、個数の計算は「COUNTIF関数」で計算できますが、条件が2つ以上ある場合などは配列数式を利用します。
下図は、各支店ごとの毎月の数量・金額の平均値を計算する計算式です。
セルC8に入力する計算式 {=AVERAGE(IF($B2:$B7="数量",C2:C7))}
セルC9に入力する計算式 {=AVERAGE(IF($B2:$B7="金額",C2:C7))}
「$B2:$B7」の計算式で、「B」の前に「$」が付いているのは、計算式を横に複写したとき検索範囲がずれないよう固定するためです。
また、計算式の「AVERAGE」を「SUM」に変更すれば、合計を求めることができます。
計算手順のイメージを説明しますと、セルB2からB7までについて「数量」という文字列と等しいかを調べ、等しければ「TRUE」を、等しくなければ「FALSE」を作業用配列に格納します。「TRUE」の場合には同じ行のC列の値を作業用配列に格納します。
最終の作業用配列を「AVERAGE関数」で計算します。
その結果 =AVERAGE(6,8,16) と同じ式になり、計算結果として「10」を求めることができます。
2 1行(列)おきに計算する
リストのデータを1行(列)おき、または指定した行(列)おきに飛び飛びに計算するときにも配列数式は威力を発揮します。
例えば、偶数行または奇数行だけの合計計算をしたい場合があります。こんなときは、「ROW関数」と「MOD関数」を併用します。「ROW関数」はセルの行番号を調べる関数、「MOD関数」は割算の余りを求める関数です。それぞれ次のような使い方をします。
ROW (A5) ・・・答えは 5 ( A5 はセル番地。「A列5行目」のセルという意味
)
MOD (13,2) ・・・答えは 1 ( 13 が数値、2 が除数。13 を 2
で割ると 1 が余る )
下図は、各支店ごとの毎月の金額は奇数行に入力されていますのでMOD関数の余りの値を
1 とします。(偶数行を求める場合は、MOD関数の余りの値を 0
とします。)
偶数行(数量)の平均 {=AVERAGE(IF(MOD(ROW($B2:$B7),2) = 0,C2:C7))}
奇数行(金額)の平均 {=AVERAGE(IF(MOD(ROW($B2:$B7),2) = 1,C2:C7))}
偶数行(数量)の合計 {=SUM((MOD(ROW($B2:$B7),2) = 0)*C2:C7)}
奇数行(金額)の合計 {=SUM((MOD(ROW($B2:$B7),2) = 1)*C2:C7)}
なお、偶数列や奇数列だけを処理したい場合には、上の式の「ROW」を「COLUMN」に変更してください。「COLUMN関数」は列番号を調べる関数です。
では、計算式の中身を詳しく見てみましょう。計算式を理解するには、(
)が沢山ある場合には内側の( )から調べていきます。
上図の赤い → の右の式は、→
の左の計算式に前の行の計算結果を適用した結果です。
計算手順のイメージを説明しますと、セルB2からB7までについて行番号が偶数か奇数かを調べ、奇数に等しければ「TRUE」を、等しくなければ「FALSE」を作業用配列に格納します。「TRUE」の場合には同じ行のC列の値を作業用配列に格納します。
最終の作業用配列を「AVERAGE関数」で計算します。
その結果 =AVERAGE(720,960,1920) と同じ式になり、計算結果として「1,200」を求めることができます。
